package com.server.questionbank.snframe.mapper.Course;

import com.server.questionbank.snframe.domain.Course.Result.CourseFigures;
import com.server.questionbank.snframe.domain.Course.TCourse;
import com.server.questionbank.snframe.domain.TInterest;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TCourseMapper {
    // 获取所有课程
    @Select("SELECT * FROM T_course")
    public List<TCourse> getAllCourse();

    // 获取课程信息
    @Select("SELECT * FROM T_course WHERE course_id = #{courseId}")
    public TCourse getCourseById(@Param("courseId") Long courseId);

    // 插入课程
    @Insert("INSERT INTO T_course (course_name, course_difficulty_id, course_description, create_by) " +
            "VALUES (#{course.courseName}, #{course.courseDifficultyId}, " +
            "#{course.courseDescription}, #{course.createBy})")
    @Options(useGeneratedKeys = true, keyProperty = "courseId")  // 指定返回生成的主键，并将其设置到 TCourse 对象的 courseId 属性
    public int insertCourse(@Param("course") TCourse course);

    // 设置封面
    @Update("UPDATE T_course SET course_cover = #{coverName} WHERE course_id = #{courseId}")
    public int setCover(@Param("courseId") Long courseId, @Param("coverName") String coverName);

    // 设置课程类型
    @Insert("INSERT INTO course_type_mapping (type_id,course_id) VALUES (#{typeId},#{courseId})")
    public int insertCourseType(@Param("typeId") Long typeId, @Param("courseId") Long courseId);


    // 设置课程标签
    @Insert("INSERT INTO course_label_mapping (label_id,course_id) VALUES (#{labelId},#{courseId})")
    public int insertCourseLabel(@Param("labelId") Long labelId, @Param("courseId") Long courseId);

    // 设置课程介绍
    @Insert("UPDATE T_course SET course_introduce = #{courseIntroduce} WHERE course_id = #{courseId};")
    public int setCourseIntroduce(@Param("courseId") Long courseId, @Param("courseIntroduce") String courseIntroduce);

    // 获取课程介绍
    @Select("SELECT course_introduce FROM T_course WHERE course_id = #{courseId};")
    public String getCourseIntroduce(@Param("courseId") Long courseId);

    // 查询课程
    @Select("<script>" +
            "<bind name=\"searchCount\"                value=\"searchList != null ? searchList.size() : 0\" />\n" +
            "<bind name=\"siftTypeCount\"              value=\"siftTypeList != null ? siftTypeList.size() : 0\" />\n" +
            "<bind name=\"siftLabelCount\"             value=\"siftLabelList != null ? siftLabelList.size() : 0\" />\n" +
            "<bind name=\"learningRecordTypeCount\"    value=\"learningRecordTypeList != null ? learningRecordTypeList.size() : 0\" />\n" +
            "<bind name=\"learningRecordLabelCount\"   value=\"learningRecordLabelList != null ? learningRecordLabelList.size() : 0\" />\n" +
            "<bind name=\"joinTypeCount\"              value=\"joinTypeList != null ? joinTypeList.size() : 0\" />\n" +
            "<bind name=\"joinLabelCount\"             value=\"joinLabelList != null ? joinLabelList.size() : 0\" />\n" +
            "<bind name=\"collectTypeCount\"           value=\"collectTypeList != null ? collectTypeList.size() : 0\" />\n" +
            "<bind name=\"collectLabelCount\"          value=\"collectLabelList != null ? collectLabelList.size() : 0\" />\n" +
            "<bind name=\"createTypeCount\"            value=\"createTypeList != null ? createTypeList.size() : 0\" />\n" +
            "<bind name=\"createLabelCount\"           value=\"createLabelList != null ? createLabelList.size() : 0\" />\n" +
            "<bind name=\"chooseTypeCount\"            value=\"chooseTypeList != null ? chooseTypeList.size() : 0\" />\n" +
            "<bind name=\"chooseLabelCount\"           value=\"chooseLabelList != null ? chooseLabelList.size() : 0\" />\n"+
            "\n" +
            "SELECT " +
            "  t.*, " +
            "  ( " +
            "    ( " +
            "        (IFNULL(t.figure_score, 0) * IFNULL(#{figureWeight}, 0.5)) + " + // 课程数据(热度)得分
            "        (IFNULL(t.user_like_score, 0) * IFNULL(#{userLikeWeight}, 0.4)) + " + // 用户喜欢得分
            "        (IFNULL(t.time_score, 0) * IFNULL(#{timeWeight}, 0.1)) " + // 时间得分
            "    ) " +
            "    <if test='explore'> * " +
            "       ( 1 + (CASE " +
            "            WHEN RAND() &lt; IFNULL(#{exploreUnpopularRatio}, 0.05) " +
            "            THEN (IFNULL(t.unpopular_explore, 0) / 100.0) * IFNULL(#{exploreUnpopularWeight}, 0.1) " +
            "            ELSE 0 " +
            "        END) + " + // 冷门探索
            "       (CASE " +
            "           WHEN RAND() &lt; IFNULL(#{randomExploreRatio}, 0.01) " +
            "               AND IFNULL(t.figure_score, 0) > 50 " +
            "               AND IFNULL(t.time_score, 0) > 50 " +
            "           THEN IFNULL(#{randomExploreWeight}, 0.03) " +
            "           ELSE 0 " +
            "       END)) " + // 随机探索占比
            "    </if>" +
            "  ) AS composite_score " +
            "FROM ( " +
            "  SELECT " +
            "    v.*, " +
            // 搜索匹配度得分字段
            "    ( " +
            "       (0 " +  // 初始分数为0
            "       <if test='searchCount > 0'> + " +
            "       \n" +
            "       <bind name=\"courseNameWeight\" value=\"0.7\" />\n " +
            "       <bind name=\"courseDescriptionWeight\" value=\"0.3\" /> \n " +
            "       <foreach collection='searchList' item='search' separator=' + '>" +
            "           ( " +
            "               (CASE " +
            "                   WHEN v.course_name = #{search} THEN (#{courseNameWeight} * 100) " +  // 名称完全匹配
            "                   WHEN LOCATE(#{search}, v.course_name) > 0 THEN " +
            "                       ROUND(CHAR_LENGTH(#{search}) * 1.0 / CHAR_LENGTH(v.course_name) * (#{courseNameWeight} * 100), 2) " + // 名称部分匹配得分
            "                   ELSE 0 " +
            "               END) " +
            "               + " +  // 名称得分结束，加上描述得分
            "               (CASE " +
            "                   WHEN v.course_description = #{search} THEN (#{courseDescriptionWeight} * 100) " + // 描述完全匹配
            "                   WHEN LOCATE(#{search}, v.course_description) > 0 THEN " +
            "                       ROUND(CHAR_LENGTH(#{search}) * 1.0 / CHAR_LENGTH(v.course_description) * (#{courseDescriptionWeight} * 100), 2) " + // 描述部分匹配得分
            "                   ELSE 0 " +
            "               END) " +
            "           ) " +
            "       </foreach> " +
            "       </if> " +
            "       ) <if test='searchCount > 0'> / #{searchCount} </if> " +  // 最后整体取平均
            "   ) AS search_match_score, " +
            // 筛选匹配得分字段
            "    ( " +
            "        0 " +  // 初始分数为0
            // 用户选择-类型
            "        <if test='siftTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='siftTypeList' item='id' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{id}, v.type_ids) > 0 THEN 1 ELSE 0 END)" +
            "        </foreach> " +
            "        ) * 100 / #{siftTypeCount} * 0.6 " +
            "        </if> " +
            // 用户选择-标签
            "        <if test='siftLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='siftLabelList' item='id' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{id}, v.label_ids) > 0 THEN 1 ELSE 0 END)" +
            "        </foreach> " +
            "        ) * 100 / #{siftLabelCount} * 0.4" +
            "        </if> " +
            "    ) AS sift_match_score, " +
            // 用户喜好得分字段
            "    ( " +
            "        0 " +  // 初始分数为0
            // 用户学习记录，加分 0.3（0.18， 0.12）
            "        <if test='learningRecordTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='learningRecordTypeList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.type_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{learningRecordTypeCount} * 0.18 " +
            "        </if> " +
            "        <if test='learningRecordLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='learningRecordLabelList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.label_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{learningRecordLabelCount} * 0.12 " +
            "        </if> " +
            // 用户加入的课程，加分 0.25（0.15， 0.1）
            "        <if test='joinTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='joinTypeList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.type_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{joinTypeCount} * 0.15 " +
            "        </if> " +
            "        <if test='joinLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='joinLabelList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.label_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{joinLabelCount} * 0.1 " +
            "        </if> " +
            // 用户收藏的课程，加分 0.2（0.12， 0.08）
            "        <if test='collectTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='collectTypeList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.type_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{collectTypeCount} * 0.12 " +
            "        </if> " +
            "        <if test='collectLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='collectLabelList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.label_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{collectLabelCount} * 0.08 " +
            "        </if> " +
            // 用户创建的课程，加分 0.15（0.09， 0.06）
            "        <if test='createTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='createTypeList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.type_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{createTypeCount} * 0.09 " +
            "        </if> " +
            "        <if test='createLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='createLabelList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.label_ids) > 0 THEN " +
            "            GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, #{interest.createDate}, NOW()) / 180, 1)), 2)) " +
            "            ELSE 0 END) " +
            "        </foreach> " +
            "        ) * 100 / #{createLabelCount} * 0.06 " +
            "        </if> " +
            // 用户选择的兴趣，加分 0.1（0.06， 0.04）
            "        <if test='chooseTypeCount > 0'> + " +
            "        ( " +
            "        <foreach collection='chooseTypeList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.type_ids) > 0 THEN 1 ELSE 0 END)" +
            "        </foreach> " +
            "        ) * 100 / #{chooseTypeCount} * 0.06 " +
            "        </if> " +
            "        <if test='chooseLabelCount > 0'> + " +
            "        ( " +
            "        <foreach collection='chooseLabelList' item='interest' separator=' + '>" +
            "            (CASE WHEN FIND_IN_SET(#{interest.interestId}, v.label_ids) > 0 THEN 1 ELSE 0 END)" +
            "        </foreach> " +
            "        ) * 100 / #{chooseLabelCount} * 0.04 " +
            "        </if> " +
            "    ) AS user_like_score, " +
            // 数据得分字段
            "    ( " +
            "        (IFNULL(v.avg_score, 0) / 10) * 50 + " +      // avg_score(1-10) 平均分占 50%
            "        (LOG(1 + IFNULL(v.student_count, 0)) / LOG(1 + 10000)) * 30 + " + // 学生数占 30%
            "        (LOG(1 + IFNULL(v.comment_count, 0)) / LOG(1 + 5000)) * 20 " +    // 评论数占 20%
            "    ) AS figure_score, " +
            // 更新时间得分字段 （365天衰减，一学年）
            "    ( " +
            "       GREATEST(0, ROUND((1 - LEAST(TIMESTAMPDIFF(DAY, v.update_time, NOW()) / 365, 1)) * 100, 2))" +
            "    ) AS time_score, " +
            // 冷门探索字段
            "    ( " +
            "        GREATEST(0, ((CASE " +
            "            WHEN TIMESTAMPDIFF(DAY, v.update_time, NOW()) &lt;= 365 " +
            "            THEN (1 - TIMESTAMPDIFF(DAY, v.update_time, NOW()) / 365.0) " +
            "            ELSE (1 - (TIMESTAMPDIFF(DAY, v.update_time, NOW()) - 365) / 365.0) * 0.5 " +
            "        END) * 0.5 " +
            "        + (1 / (1 + IFNULL(v.student_count, 0))) * 0.25 " +
            "        + (1 / (1 + IFNULL(v.comment_count, 0))) * 0.25 " +
            "        ), 2) * 100 " +
            "    ) AS unpopular_explore " +
            // 筛选条件
            " FROM v_course_full_info v " +
            " WHERE 1 = 1 " +
            "   <if test='memberCourseId != null and memberCourseId > 0'> " +
            "       AND EXISTS ( " +
            "            SELECT 1 " +
            "            FROM user_regist_cus cm1 " +
            "            JOIN user_regist_cus cm2 ON cm1.user_id = cm2.user_id " +
            "            WHERE cm1.course_id = #{memberCourseId} " +
            "               AND cm2.course_id = v.course_id " +
            "       ) " +
            "   </if> " +
            "    <if test='courseId != null and courseId > 0'> " +
            "       AND v.course_id = #{courseId} " +
            "    </if> " +
            "    <if test='teacherId != null and teacherId > 0'> " +
            "       AND v.create_by = #{teacherId} " +
            "    </if> " +
            "    <if test='excludeCourseIdList != null and excludeCourseIdList.size() > 0'> " +
            "       AND v.course_id NOT IN " +
            "       <foreach collection='excludeCourseIdList' item='id' open='(' separator=',' close=')'> " +
            "            #{id} " +
            "       </foreach> " +
            "    </if>" +
            "    <if test='searchCount > 0'>" +
            "      AND (" +
            "       <foreach collection='searchList' item='search' separator=' OR '>" +
            "           ((v.course_name LIKE CONCAT('%', #{search}, '%'))" +
            "           OR (v.course_description LIKE CONCAT('%', #{search}, '%')))" +
            "       </foreach> " +
            "      )" +
            "    </if> " +
            "    <if test='difficultyId != null and difficultyId > 0'> AND v.course_difficulty_id = #{difficultyId} </if> " +
            ") t " +
            // 排序
            "ORDER BY " +
            "t.search_match_score DESC, t.sift_match_score DESC, " + // 优先筛选排序
            "composite_score DESC, " + // 综合数据排序
            "t.user_like_score DESC, " + // 用户喜好排序
            "t.figure_score DESC, t.time_score DESC, " + // 数据排序
            "t.update_time DESC, t.course_id DESC " + // 兜底时间和ID排序
            // 分页
            "<if test='offset != null and pageSize != null'> LIMIT #{pageSize} OFFSET #{offset} </if> " +
            "</script>")
    List<CourseFigures> findCourseByWeight(
            @Param("courseId") Long courseId,
            @Param("memberCourseId") Long memberCourseId, // 课程成员课程ID，用于筛选已加入课程
            @Param("teacherId") Long teacherId,
            @Param("searchList") List<String> searchList,
            @Param("difficultyId") Long difficultyId,
            @Param("siftTypeList") List<Long> siftTypeList,
            @Param("siftLabelList") List<Long> siftLabelList,
            @Param("learningRecordTypeList") List<TInterest> learningRecordTypeList, // 学习记录-类型
            @Param("learningRecordLabelList") List<TInterest> learningRecordLabelList, // 学习记录-标签
            @Param("joinTypeList") List<TInterest> joinTypeList, // 加入-类型
            @Param("joinLabelList") List<TInterest> joinLabelList, // 加入-标签
            @Param("collectTypeList") List<TInterest> collectTypeList, // 收藏-类型
            @Param("collectLabelList") List<TInterest> collectLabelList, // 收藏-标签
            @Param("createTypeList") List<TInterest> createTypeList, // 创建-类型
            @Param("createLabelList") List<TInterest> createLabelList, // 创建-标签
            @Param("chooseTypeList") List<TInterest> chooseTypeList, // 用户选择-类型
            @Param("chooseLabelList") List<TInterest> chooseLabelList, // 用户选择-标签
            @Param("excludeCourseIdList") List<Long> excludeCourseIdList, // 排除课程ID
            @Param("offset") Long offset, // 偏移量
            @Param("pageSize") Long pageSize, // 每页数量
            @Param("figureWeight") Double figureWeight,      // ⭐️ 数据得分权重
            @Param("userLikeWeight") Double userLikeWeight,  // ⭐️ 用户偏好得分权重
            @Param("timeWeight") Double timeWeight,          // ⭐️ 更新时间得分权重
            @Param("explore") Boolean explore,     // ⭐️ 是否开启探索
            @Param("exploreUnpopularWeight") Double exploreUnpopularWeight,    // ⭐️ 冷门探索权重
            @Param("exploreUnpopularRatio") Double exploreUnpopularRatio,    // ⭐️ 冷门探索概率
            @Param("randomExploreWeight") Double randomExploreWeight,    // ⭐️ 随机探索权重
            @Param("randomExploreRatio") Double randomExploreRatio    // ⭐️ 随机探索概率
    );
    // 查询课程的数量
    @Select("<script>" +
            "SELECT " +
            "    COUNT(v.course_id) " +
            // 筛选条件
            "FROM v_course_full_info v " +
            "WHERE 1 = 1 " +
            "   <if test='memberCourseId != null and memberCourseId > 0'> " +
            "       AND EXISTS ( " +
            "            SELECT 1 " +
            "            FROM user_regist_cus cm1 " +
            "            JOIN user_regist_cus cm2 ON cm1.user_id = cm2.user_id " +
            "            WHERE cm1.course_id = #{memberCourseId} " +
            "               AND cm2.course_id = v.course_id " +
            "       ) " +
            "   </if> " +
            "    <if test='courseId != null and courseId > 0'> " +
            "       AND v.course_id = #{courseId} " +
            "    </if> " +
            "    <if test='teacherId != null and teacherId > 0'> " +
            "       AND v.create_by = #{teacherId} " +
            "    </if> " +
            "    <if test='excludeCourseIdList != null and excludeCourseIdList.size() > 0'> " +
            "       AND v.course_id NOT IN " +
            "       <foreach collection='excludeCourseIdList' item='id' open='(' separator=',' close=')'> " +
            "            #{id} " +
            "       </foreach> " +
            "    </if>" +
            "    <if test='searchList != null and searchList.size() > 0'> " +
            "      AND (" +
            "       <foreach collection='searchList' item='search' separator=' OR '>" +
            "           ((v.course_name LIKE CONCAT('%', #{search}, '%'))" +
            "           OR (v.course_description LIKE CONCAT('%', #{search}, '%')))" +
            "       </foreach> " +
            "      )" +
            "    </if> " +
            "    <if test='difficultyId != null and difficultyId > 0'> AND v.course_difficulty_id = #{difficultyId} </if> " +
            "</script>")
    Long findCourseByCount(
            @Param("courseId") Long courseId,
            @Param("memberCourseId") Long memberCourseId, // 课程成员课程ID，用于筛选已加入课程
            @Param("teacherId") Long teacherId,
            @Param("excludeCourseIdList") List<Long> excludeCourseIdList, // 排除课程ID
            @Param("searchList") List<String> searchList,
            @Param("difficultyId") Long difficultyId
    );

}
